﻿Public Class frmMember
    Dim dsMem As New DataSet
    Dim daMem As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub frmMember_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub frmMember_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Call gSubIconShow(Me)
        Call dbConnection()
        Call gSubMkCmbCompany("", cmbCompany)
        Call gSubMkCmbMemFlag(cmbFlag)
        cmbFlag.SelectedIndex = 0
        Call gridRefresh("default")
        Call grid2Refresh("0")
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        Call saveMemInfo()
    End Sub

    Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
        Call gridRefresh("")
    End Sub

    Private Sub btnSearchReset_Click(sender As System.Object, e As System.EventArgs) Handles btnSearchReset.Click
        txtSearch.Clear()
        Call gridRefresh("")
    End Sub

    Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
        Call txtReset()
    End Sub

    Private Sub cmbCompany_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbCompany.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbFlag_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbFlag.KeyPress
        e.Handled = True
    End Sub

#Region "프로시져"
    Private Sub gridRefresh(ByVal initFlag As String)

        Call dbConnection()
        Dim addSql As String = ""

        dsMem = New DataSet
        If txtSearch.Text <> "" Then
            addSql = " AND A.memNm LIKE '%" + txtSearch.Text + "%' "
        End If
        If initFlag = "default" Then
            addSql = " AND A.memId=0 "
        End If
        sql = "SELECT A.memId, A.memNm as 이름, A.birthday as 생년월일, B.company as 회사명, A.cellPhone as 연락처, A.email as [E-mail], A.addr as 주소, IIF(A.flag='H','정지','유효') as 유효 " & _
            "   FROM tblMember A LEFT JOIN tblCompany B ON A.compCd=B.compCd WHERE A.flag<>'N'" + addSql

        Try
            daMem = New OleDb.OleDbDataAdapter(sql, dbConn)
            daMem.Fill(dsMem, "Member")

            grdMember.DataSource = dsMem.Tables("Member")
            lbGridCnt.Text = "총 " + dsMem.Tables("Member").Rows.Count.ToString + "건"

            dbConn.Close()
            Call setGridDesign()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGridDesign()

        grdMember.Columns(0).Visible = False
        grdMember.Columns(1).Width = "90"
        grdMember.Columns(2).Width = "90"
        grdMember.Columns(3).Width = "120"
        grdMember.Columns(4).Width = "100"
        grdMember.Columns(5).Width = "120"
        grdMember.Columns(6).Width = "150"
        grdMember.Columns(7).Width = "70"

        With grdMember.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter

        End With
        grdMember.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        grdMember.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        grdMember.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        Call setHolding()
    End Sub

    Private Sub setHolding()
        For i = 0 To grdMember.Rows.Count - 1
            If IsNothing(grdMember.Rows(i).Cells(7).Value) = False Then
                If grdMember.Rows(i).Cells(7).Value = "정지" Then
                    grdMember.Rows(i).DefaultCellStyle.BackColor = Color.LightGray
                End If
            End If
        Next
    End Sub

    Private Sub grid2Refresh(ByVal uid As String)

        Call dbConnection()

        Dim dsMemPrd As New DataSet
        Dim daMemPrd As OleDb.OleDbDataAdapter
        sql = "SELECT B.prdNm as 제품, A.issueDt as 구매일, A.amount as 결재금액, A.[output] as 수량, A.unitPrice as 단가, A.totalPrice as 총금액, A.dcRate+'%' as 할인율, A.dcAmount as 할인금액 " + _
            "   FROM tblPrdInOut A LEFT JOIN tblProduct B ON A.prdCd=B.prdCd WHERE A.flag='Y' AND A.memId=" + uid + " ORDER BY A.issueDt DESC"
        'MsgBox(sql)
        Try
            daMemPrd = New OleDb.OleDbDataAdapter(sql, dbConn)
            daMemPrd.Fill(dsMemPrd, "MemProduct")

            grdBuyHistory.DataSource = dsMemPrd.Tables("MemProduct")
            grdBuyHistory.Columns(0).Frozen = True

            dbConn.Close()
            Call setGrid2Design()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub setGrid2Design()

        grdBuyHistory.Columns(0).Width = "80"
        grdBuyHistory.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
        grdBuyHistory.Columns(1).Width = "70"
        grdBuyHistory.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        grdBuyHistory.Columns(2).Width = "90"
        grdBuyHistory.Columns(2).DefaultCellStyle.Format = "n0"
        grdBuyHistory.Columns(3).Width = "60"
        grdBuyHistory.Columns(3).DefaultCellStyle.Format = "n0"
        grdBuyHistory.Columns(4).Width = "80"
        grdBuyHistory.Columns(4).DefaultCellStyle.Format = "n0"
        grdBuyHistory.Columns(5).Width = "90"
        grdBuyHistory.Columns(5).DefaultCellStyle.Format = "n0"
        grdBuyHistory.Columns(6).Width = "70"
        grdBuyHistory.Columns(6).DefaultCellStyle.Format = "n0"
        grdBuyHistory.Columns(7).Width = "90"
        grdBuyHistory.Columns(7).DefaultCellStyle.Format = "n0"

        With grdBuyHistory.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleRight

        End With
    End Sub

    Private Sub grdMember_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdMember.CellClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdMember.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If
        sql = "SELECT memId, memNm, birthday, compCd, cellPhone, email, addr, remarks, flag FROM tblMember WHERE memId=" & grdMember.Rows(e.RowIndex).Cells(0).Value

        Call dbConnection()
        dsMem = New DataSet
        daMem = New OleDb.OleDbDataAdapter(sql, dbConn)
        daMem.Fill(dsMem, "memInfo")

        dbConn.Close()

        txtReset()

        Call grid2Refresh(dsMem.Tables("memInfo").Rows(0).Item(0))
        txtMemId.Text = dsMem.Tables("memInfo").Rows(0).Item(0)
        txtMemNm.Text = dsMem.Tables("memInfo").Rows(0).Item(1)

        If IsDBNull(dsMem.Tables("memInfo").Rows(0).Item(2)) = False Then
            txtBirthday.Text = dsMem.Tables("memInfo").Rows(0).Item(2)
        End If
        If IsDBNull(dsMem.Tables("memInfo").Rows(0).Item(3)) = False Then
            cmbCompany.SelectedValue = dsMem.Tables("memInfo").Rows(0).Item(3)
        End If
        If IsDBNull(dsMem.Tables("memInfo").Rows(0).Item(4)) = False Then
            txtPhone.Text = dsMem.Tables("memInfo").Rows(0).Item(4)
        End If
        If IsDBNull(dsMem.Tables("memInfo").Rows(0).Item(5)) = False Then
            txtEmail.Text = dsMem.Tables("memInfo").Rows(0).Item(5)
        End If
        If IsDBNull(dsMem.Tables("memInfo").Rows(0).Item(6)) = False Then
            txtAddr.Text = dsMem.Tables("memInfo").Rows(0).Item(6)
        End If
        If IsDBNull(dsMem.Tables("memInfo").Rows(0).Item(7)) = False Then
            txtRemarks.Text = dsMem.Tables("memInfo").Rows(0).Item(7)
        End If
        If IsDBNull(dsMem.Tables("memInfo").Rows(0).Item(8)) = False Then
            cmbFlag.SelectedValue = dsMem.Tables("memInfo").Rows(0).Item(8)
        End If
    End Sub

    Private Sub saveMemInfo()

        Call dbConnection()
        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim rtnMsg As String
        Dim valCompCd, valMemId, valMemNm, valBirthday, valEmail, valCellPhone, valAddr, valRemarks, valFlag As String
        Dim curDt As DateTime = Now

        valCellPhone = gFnChkVal(txtPhone.Text)
        valMemNm = gFnChkVal(txtMemNm.Text)
        valBirthday = gFnChkVal(txtBirthday.Text)
        valEmail = gFnChkVal(txtEmail.Text)
        valRemarks = gFnChkVal(txtRemarks.Text)
        valAddr = gFnChkVal(txtAddr.Text)
        valMemId = gFnChkVal(txtMemId.Text)
        valCompCd = cmbCompany.SelectedValue
        valFlag = cmbFlag.SelectedValue

        If valMemNm = "" Then
            MsgBox("이름을 바르게 입력해 주세요")
            txtMemNm.Focus()
            dbConn.Close()
            Exit Sub
        End If


        If txtMemId.Text = "" Then
            sql = "INSERT INTO tblMember(memNm, birthday, compCd, cellPhone, email, addr, remarks, flag, addDt) VALUES('" + valMemNm + "','" + valBirthday + "','" + valCompCd + "','" + valCellPhone + "','" + valEmail + "','" + valAddr + "','" + valRemarks + "','" + valFlag + "','" + curDt + "')"
            rtnMsg = "회원 정보가 등록되었습니다"
        Else
            sql = "UPDATE tblMember SET memNm='" + valMemNm + "', birthday='" + valBirthday + "', compCd='" + valCompCd + "', cellphone='" + valCellPhone + "', email='" + valEmail + "', addr='" + valAddr + "', remarks='" + valRemarks + "', flag='" + valFlag + "', updateDt='" + curDt + "' WHERE memId=" + txtMemId.Text
            rtnMsg = "회원 정보가 수정되었습니다"
        End If

        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            dbConn.Close()
            MsgBox(rtnMsg)
            Call gridRefresh("")
            Call txtReset()
        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub txtReset()
        txtMemId.Clear()
        txtMemNm.Clear()
        txtBirthday.Clear()
        txtPhone.Clear()
        txtEmail.Clear()
        txtAddr.Clear()
        txtRemarks.Clear()
        gSubMkCmbCompany("", cmbCompany)
        cmbFlag.SelectedIndex = 0
        Call grid2Refresh(0)
    End Sub
#End Region
End Class